package core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;
import org.apache.log4j.Logger;

/**
 * A singleton class which represents a single-instance database connection.
 * If necessary, creates all data objects (tables, indices and sequences) 
 * in the database when the instance of class is created.
 * @author Danon
 */
@ManagedBean
@ApplicationScoped
public class Database {

    private static final Logger logger = Logger.getLogger(Database.class.getName());
    
    private Database() throws ClassNotFoundException, SQLException {
        logger.info("Initializing connection to database");
        Class.forName("oracle.jdbc.driver.OracleDriver");
        connection = DriverManager.getConnection(CONNECTION_STRING, "anton", "anton");
        logger.info("Connected to database");
        initSchema();
    }

    /**
     * Returns single instance object of this class. Connects to the database if necessary.
     * @return single instance of Database
     * @throws ClassNotFoundException driver class not found
     * @throws SQLException Connection filed.
     */
    synchronized public static Database getInstance() throws ClassNotFoundException, SQLException {
        if (instance == null) {
            instance = new Database();
        }
        return instance;
    }

    /**
     * Returns connection to the database.
     * @return connection to database
     */
    public synchronized Connection getConnection() {
        return connection;
    }

    /**
     * Resets current connection: commit and close
     * The next call of getInstance() will recreate the instance.
     */
    public synchronized void reset() {
        try {
            if (connection != null) {
                connection.commit();
                connection.close();
            }
        } catch (SQLException ex) {
            logger.error("Failed to close connection to database.", ex);
        }
        connection = null;
        instance = null;
        logger.info("Disconnected from database");
    }

    /**
     * Creates all necessary tables, indices and sequences.
     * @throws SQLException Something went wrong while committing.
     */
    private void initSchema() throws SQLException {
        logger.info("Initializing schema");
        executeUpdate("create sequence gen_user_id nomaxvalue nocycle");
        executeUpdate("create sequence gen_engagement_id nomaxvalue nocycle");
        executeUpdate("create sequence gen_router_id nomaxvalue nocycle");
        executeUpdate("create sequence gen_order_id nomaxvalue nocycle");
        executeUpdate("create sequence gen_link_id nomaxvalue nocycle");
        executeUpdate("CREATE TABLE users (user_id number primary key, "
                    + "login varchar2(20) unique not null, "
                    + "password varchar2(50) not null, "
                    + "full_name varchar2(100) not null, "
                    + "role varchar2(10) default 'USER')");
        executeUpdate("CREATE TABLE routers (router_id number primary key, "
                    + "title varchar2(255) unique not null)");
        executeUpdate("create table links ("
                + "link_id number primary key,"
                + "title varchar2(255) not null,"
                + "src_router number not null,"
                + "dst_router number not null,"
                + "bandwidth number(12, 3) not null,"
                + "constraint fk_router1 foreign key  (src_router) references routers(router_id) on delete cascade,"
                + "constraint fk_router2 foreign key (dst_router) references routers(router_id) on delete cascade,"
                + "constraint chk_negative_bandwidth check (bandwidth>=0)"
                + ")");
        executeUpdate("create table orders ("
                + "order_id number primary key,"
                + "customer_id number not null,"
                + "order_date date default sysdate not null,"
                + "src_router number not null,"
                + "dst_router number not null,"
                + "bandwidth number(12, 3) not null,"
                + "status varchar2(20) not null,"
                + "completion_date date,"                
                + "order_comment varchar2(255),"
                + "constraint fk_customer foreign key (customer_id) references users(user_id) on delete cascade,"
                + "constraint fk_src_router foreign key (src_router) references routers(router_id) on delete cascade,"
                + "constraint fk_dst_router foreign key (dst_router) references routers(router_id) on delete cascade"
                + ")");
        // create table for engagements (channel_id, order_id, path, status)
        executeUpdate("create table engagements ( "
                + "engagenent_id number primary key,"
                + "order_id int not null,"
                + "link_id int not null,"
                + "num int not null,"
                + "constraint fk_order_id foreign key (order_id) references orders(order_id) on delete cascade,"
                + "constraint fk_link_id foreign key (link_id) references links(link_id) on delete cascade,"
                + "constraint chk_num check (num >= 0)"
                + ")");
        // create indexes
        executeUpdate("create index src_router_idx on links(src_router)");
        executeUpdate("create index dst_router_idx on links(dst_router)");
        executeUpdate("create index idx_order_id on engagements(order_id)");
        executeUpdate("create index idx_link_id on engagements(link_id)");
        connection.commit();
        logger.info("Schema initialized.");
    }

    /** 
     * Runs DDL/DML SQL commend for current database connection. 
     * Commit is not performed.
     */
    private int executeUpdate(String sql) {
        int res = -1;
        try {
            try (PreparedStatement ps = connection.prepareStatement(sql)) {
                res = ps.executeUpdate();
            }
        } catch (Exception ex) {
            logger.debug("Failed to exucute query: "+sql+"\n\t due to exception: " + ex.getMessage());
        }
        return res;
    }
    
    private Connection connection;
    private volatile static Database instance;
    public static final String CONNECTION_STRING = "jdbc:oracle:thin:@localhost:1521:xe";
}
